﻿//==================================================================
// Copyright (C) 2015 
// 作 者：
// Email: 
// 日 期：
// 描 述：
// 版 本：1.0.0.0    
//    
//==================================================================

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using Aspose.Cells;
using System.Collections.ObjectModel;
using System.Data;

namespace FL.Aspose
{
    public class ImportCell
    {
        #region 属性
        /// <summary>
        /// 文件名称,含绝对路径.如E:\TEST.XLS
        /// </summary>
        private string filePath = "";
        /// <summary>
        /// 文件名称,含绝对路径.如E:\TEST.XLS
        /// </summary>
        public string FilePath
        {
            get { return this.filePath; }
            set { this.filePath = value; }
        }

        /// <summary>
        /// 配置需要导入的列
        /// </summary>
        private List<ImportColumnConfig> lstImportColumnConfig = new List<ImportColumnConfig>();
        /// <summary>
        /// 配置需要导入的列
        /// </summary>
        public List<ImportColumnConfig> LstImportColumnConfig
        {
            get { return this.lstImportColumnConfig; }
            set { this.lstImportColumnConfig = value; }
        }

        /// <summary>
        /// sheet页列索引，格式【sheet名，【列中文名，索引号】】
        /// </summary>
        private Dictionary<string, Dictionary<string, int>> collIndexStore = new Dictionary<string, Dictionary<string, int>>();
        /// <summary>
        /// sheet页列索引，格式【sheet名，【列中文名，索引号】】
        /// </summary>
        public Dictionary<string, Dictionary<string, int>> CollIndexStore
        {
            get { return this.collIndexStore; }
            set { this.collIndexStore = value; }
        }

        /// <summary>
        /// //栏位配置级别错误校验信息
        /// </summary>
        private List<string> columnConfigErrorList = new List<string>();
        /// <summary>
        /// //栏位配置级别错误校验信息
        /// </summary>
        private List<string> ColumnConfigErrorList
        {
            get { return this.columnConfigErrorList; }
            set { this.columnConfigErrorList = value; }
        }

        /// <summary>
        /// //单元格级别校验错误信息
        /// </summary>
        private List<string> validateErrorList = new List<string>();
        /// <summary>
        /// 单元格级别校验错误信息
        /// </summary>
        private List<string> ValidateErrorList
        {
            get { return this.validateErrorList; }
            set { this.validateErrorList = value; }
        }

        /// <summary>
        /// 读取的sheet对像
        /// </summary>
        List<SheetEntity> sheets = new List<SheetEntity>();
        /// <summary>
        /// 读取的sheet对像
        /// </summary>
        public List<SheetEntity> Sheets
        {
            get { return this.sheets; }
            set { this.sheets = value; }
        }

        #endregion

        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="filePath"></param>
        public ImportCell(string filePath)
        {
            this.filePath = filePath;
        }

        #region 校验
        /// <summary>
        /// 读取并校验Sheet
        /// </summary>
        /// <returns></returns>
        public bool ImportAndCheckSheets()
        {
            bool result = true;
            try
            {
                if (!IsImportColumnConfig())
                {
                    validateErrorList.Add("未配置需要导入的列");
                    result = false;
                }
                else
                {
                    sheets = GetSheetData();
                    if (columnConfigErrorList.Count == 0) //栏位配置正确，或者没有有重复行数据
                    {
                        //单元格数据校验
                        ValidateData(sheets);
                        result = validateErrorList.Count > 0;//单元格校验不正确
                    }
                    else
                    {
                        result = false;
                    }
                }
            }
            catch (Exception ex)
            {
                throw;
            }
            return result;
        }


        /// <summary>
        /// 导入列是否配置了
        /// </summary>
        /// <returns></returns>
        public bool IsImportColumnConfig()
        {
            if (this.lstImportColumnConfig.Count > 0)
            {
                return true;
            }
            return false;
        }

        /// <summary>
        /// excel栏位配置校验
        /// </summary>
        /// <param name="sheet"></param>
        public void ValidateColumn(SheetEntity sheet)
        {
            CheckColumnConfig(sheet, LstImportColumnConfig);
        }

        /// <summary>
        /// 必填栏位校验
        /// </summary>
        /// <param name="sheet"></param>
        /// <param name="?"></param>
        /// <param name="errorlist"></param>
        public void CheckColumnConfig(SheetEntity sheet, List<ImportColumnConfig> columns)
        {
            List<string> fileds = new List<string>();
            foreach (ImportColumnConfig config in columns)
            {
                fileds.Add(config.FieldCNName);
            }
            foreach (string colName in fileds)
            {
                if (sheet.FieldEntitys.FirstOrDefault(a => a.FieldCNName == colName) == null)
                {
                    columnConfigErrorList.Add(string.Format("{0}:缺少{1}列!", sheet.SheetName, colName));
                }
            }
        }

        /// <summary>
        /// 单元格数据校验
        /// </summary>
        /// <param name="shetts"></param>
        /// <returns></returns>
        public void ValidateData(List<SheetEntity> sheets)
        {
            foreach (SheetEntity sheet in sheets)
            {
                CheckSheetCellDate(sheet);
            }
        }

        /// <summary>
        /// 校验Sheet数据
        /// </summary>
        /// <param name="sheet"></param>
        public void CheckSheetCellDate(SheetEntity sheet)
        {
            if (sheet.SampleData == null)
            {
                return;
            }
            int rowCount = sheet.SampleData.Count;
            foreach (FieldEntity fieldEntity in sheet.FieldEntitys)
            {
                int j = fieldEntity.Index;
                //获取当前列的配置信息
                ImportColumnConfig columnfig = lstImportColumnConfig.Where(p => p.FieldCNName == fieldEntity.FieldCNName).FirstOrDefault();
                if (columnfig == null)//必填列的特殊校验
                {
                    continue;
                }
                for (int i = 0; i < rowCount; i++)
                {
                    string cellvalue = sheet.SampleData[i][j] == null ? string.Empty : sheet.SampleData[i][j].ToString();

                    if (!columnfig.IsNullable)
                    {//空值判断
                        if (string.IsNullOrEmpty(cellvalue))
                        {
                            validateErrorList.Add(string.Format("{0}sheet页下第{1}行{2}列的数据不能为空!", sheet.SheetName, i + 2, fieldEntity.FieldCNName));
                        }
                    }
                    if (cellvalue.Length > columnfig.MaxLength)
                    {//输入长度校验
                        validateErrorList.Add(string.Format("{0}sheet页下第{1}行{2}列的数据长度不能超过{3}!", sheet.SheetName, i + 2, fieldEntity.FieldCNName, columnfig.MaxLength));
                    }
                }
            }
        }

        #endregion

        #region 获取Sheet数据
        /// <summary>
        /// 获取Sheet数据
        /// </summary>
        /// <param name="sheetName">页名称</param>
        /// <returns></returns>
        public List<SheetEntity> GetSheetData()
        {
            List<SheetEntity> sheets = new List<SheetEntity>();
            try
            {
                Workbook workBook = CreateWorkbook();//虚拟一个Excel文件
                if (workBook.Worksheets.Count == 0)
                {
                    return null;
                }
                string fileName = Path.GetFileName(filePath);
                foreach (Worksheet sheet in workBook.Worksheets)
                {
                    SheetEntity SheetEntity = new SheetEntity();
                    SheetEntity.SheetName = sheet.Name;
                    SheetEntity.FileName = fileName;

                    Dictionary<string, int> colindexdic = new Dictionary<string, int>();//列索引字典
                    this.CollIndexStore.Add(sheet.Name, colindexdic);

                    var cells = sheet.Cells;//得到sheet的所有工作单元格
                    if (cells.MaxDataRow >= 1)
                    {
                        for (int j = 0; j <= cells.MaxDataColumn; j++)//判断Excel的行数跟列数
                        {
                            //要使用MaxDataColumn和MaxDataRow属性
                            FieldEntity fieldEntity = new FieldEntity();
                            fieldEntity.Index = j;
                            fieldEntity.FieldName = cells[0, j].StringValue.Trim();
                            fieldEntity.FieldCNName = cells[0, j].StringValue.Trim();
                            SheetEntity.FieldEntitys.Add(fieldEntity);
                            colindexdic[fieldEntity.FieldCNName] = j;
                        }

                        //校验配置是否配置正确
                        ValidateColumn(SheetEntity);
                        if (columnConfigErrorList.Count > 0)//暂时屏蔽掉，最后需要加上，
                        {
                            //栏位未配置正确
                            continue;
                        }
                        SheetEntity.SampleData = new ObservableCollection<ObservableCollection<object>>();
                        for (int i = 0; i < cells.MaxDataRow; i++)
                        {
                            ObservableCollection<object> list = new ObservableCollection<object>();
                            for (int j = 0; j <= cells.MaxDataColumn; j++)
                            {
                                object cellValue = cells[i + 1, j].Value == null ? "" : cells[i + 1, j].Value;
                                list.Add(cellValue);//取得excel单元的内容
                            }
                            SheetEntity.SampleData.Add(list);
                        }
                    }
                    sheets.Add(SheetEntity);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return sheets;
        }

        #endregion

        #region ImportToTable
        /// <summary>
        ///  读取EXCEL中的一个sheet数据到DataTable
        /// </summary>
        /// <param name="sheetName">sheet名字</param>
        /// <returns></returns>
        public DataTable ImportToTable(string sheetName)
        {
            DataTable dt = new DataTable();
            try
            {
                //校验
                if (ImportAndCheckSheets())
                {
                    foreach (SheetEntity item in sheets)
                    {
                        if (item.SheetName.ToUpper() == sheetName.ToUpper())
                        {
                            dt = ImportToTable(item);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw;
            }
            return dt;
        }

        /// <summary>
        /// 读取EXCEL中的一个sheet数据到DataTable
        /// </summary>
        /// <param name="sheet">sheet对像</param>
        /// <returns></returns>
        public DataTable ImportToTable(SheetEntity sheet)
        {
            DataTable dt = new DataTable();
            try
            {
                dt.TableName = sheet.SheetName.ToLower();
                //初始化DataTable
                foreach (ImportColumnConfig item in lstImportColumnConfig)
                {
                    DataColumn col = new DataColumn();
                    col.ColumnName = item.FieldName;
                    dt.Columns.Add(col);
                }

                if (sheet.SampleData != null)
                {
                    int rowCount = sheet.SampleData.Count;
                    //读取单元格数据
                    for (int i = 0; i < rowCount; i++)
                    {
                        DataRow dr = dt.NewRow();
                        foreach (FieldEntity fieldEntity in sheet.FieldEntitys)
                        {
                            foreach (ImportColumnConfig item in lstImportColumnConfig)
                            {
                                if (item.FieldName == fieldEntity.FieldName)
                                {
                                    string cellvalue = sheet.SampleData[i][fieldEntity.Index] == null ? string.Empty : sheet.SampleData[i][fieldEntity.Index].ToString();
                                    dr[fieldEntity.FieldName] = cellvalue;
                                }
                            }
                        }
                        dt.Rows.Add(dr);
                    }
                }
            }
            catch (Exception ex)
            {
                throw;
            }
            return dt;
        }

        /// <summary>
        /// 读取EXCEL中的所有sheet数据到DataSet
        /// 每个sheet为一个DataTable
        /// </summary>
        /// <returns></returns>
        public DataSet ImportToDataSet()
        {
            DataSet ds = new DataSet();
            try
            {
                //校验
                if (ImportAndCheckSheets())
                {
                    foreach (SheetEntity item in sheets)
                    {
                        DataTable dt = ImportToTable(item);
                        if (!ds.Tables.Contains(dt.TableName) && dt.Rows.Count > 0)
                        {
                            ds.Tables.Add(dt);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw;
            }
            return ds;
        }
        #endregion

        #region 直接读取EXCEL到DataSet
        /// <summary>
        /// 直接读取EXCEL到DataSet
        /// </summary>
        /// <returns></returns>
        public DataSet GetSheetDataToDataSet()
        {
            DataSet ds = new DataSet();
            try
            {
                Workbook workBook = CreateWorkbook();//虚拟一个Excel文件
                if (workBook.Worksheets.Count == 0)
                {
                    return null;
                }
                foreach (Worksheet sheet in workBook.Worksheets)
                {
                    var cells = sheet.Cells;//得到sheet的所有工作单元格
                    DataTable dt = new DataTable();
                    if (cells.MaxDataRow > 0 && cells.MaxDataColumn > 0)
                    {
                        dt = cells.ExportDataTableAsString(0, 0, cells.MaxDataRow + 1, cells.MaxDataColumn + 1, true);
                        dt.TableName = sheet.Name.ToLower();
                    }
                    if (!ds.Tables.Contains(dt.TableName) && dt.Rows.Count > 0)
                    {
                        ds.Tables.Add(dt);
                    }
                }
            }
            catch (Exception ex)
            {
                throw;
            }
            return ds;
        }

        /// <summary>
        /// 读取sheet到DataTable,一个个单元格读取
        /// </summary>
        /// <param name="sheet"></param>
        /// <returns></returns>
        public DataTable GetSheetDataToDataTable(Worksheet sheet)
        {
            DataTable dt = new DataTable();
            try
            {
                var cells = sheet.Cells;//得到sheet的所有工作单元格
                dt.TableName = sheet.Name.ToLower();
                Dictionary<string, int> colindexdic = new Dictionary<string, int>();//列索引字典
                //先添加表头
                if (cells.MaxDataRow >= 1)
                {

                    for (int j = 0; j <= cells.MaxDataColumn; j++)//判断Excel的行数跟列数
                    {
                        DataColumn dtCol = new DataColumn();
                        string columnName = cells[0, j].StringValue.Trim();
                        dtCol.ColumnName = columnName;
                        dt.Columns.Add(dtCol);
                        colindexdic[columnName] = j;
                    }
                }

                //添加数据
                if (cells.MaxDataRow >= 1)
                {
                    for (int i = 0; i < cells.MaxDataRow; i++)
                    {
                        DataRow dr = dt.NewRow();
                        for (int j = 0; j <= cells.MaxDataColumn; j++)
                        {
                            object cellValue = cells[i + 1, j].Value == null ? "" : cells[i + 1, j].Value;
                            dr[j] = cellValue;
                        }
                        dt.Rows.Add(dr);
                    }
                }
            }
            catch (Exception ex)
            {
                throw;
            }
            return dt;
        }


        /// <summary>
        /// 读取sheet到DataTable,一个个单元格读取
        /// </summary>
        /// <param name="sheet"></param>
        /// <returns></returns>
        public DataTable GetSheetDataToDataTable(Worksheet sheet,string tableName)
        {
            DataTable dt = new DataTable();
            try
            {
                var cells = sheet.Cells;//得到sheet的所有工作单元格
                dt.TableName = sheet.Name.ToLower();
                Dictionary<string, int> colindexdic = new Dictionary<string, int>();//列索引字典
                //先添加表头
                if (cells.MaxDataRow >= 1)
                {

                    for (int j = 0; j <= cells.MaxDataColumn; j++)//判断Excel的行数跟列数
                    {
                        DataColumn dtCol = new DataColumn();
                        string columnName = cells[0, j].StringValue.Trim();
                        dtCol.ColumnName = columnName;
                        dt.Columns.Add(dtCol);
                        colindexdic[columnName] = j;
                    }
                }

                //添加数据
                if (cells.MaxDataRow >= 1)
                {
                    for (int i = 0; i < cells.MaxDataRow; i++)
                    {
                        DataRow dr = dt.NewRow();
                        for (int j = 0; j <= cells.MaxDataColumn; j++)
                        {
                            object cellValue = cells[i + 1, j].Value == null ? "" : cells[i + 1, j].Value;
                            dr[j] = cellValue;
                        }
                        dt.Rows.Add(dr);
                    }
                }
            }
            catch (Exception ex)
            {
                throw;
            }
            return dt;
        }
        #endregion

        /// <summary>
        /// 创建虚拟一个Excel文件
        /// </summary>
        /// <returns></returns>
        private Workbook CreateWorkbook()
        {
            Workbook workBook = new Workbook();
            try
            {
                string fileName = Path.GetFileName(filePath);
                if (File.Exists(filePath))
                {
                    string extension = Path.GetExtension(filePath);//获取文件的后缀名
                    FileStream fs = new FileStream(filePath, FileMode.Open);  

                    switch (extension.ToUpper())
                    {
                        case ".XLS":
                            //workBook.Open(filePath);//打开文件
                            workBook.Open(fs);//打开文件
                            break;
                        case ".XLSX":
                            workBook.Open(fs, FileFormatType.Excel2007Xlsx);//打开文件
                            break;
                    }
                }
                else
                {
                    validateErrorList.Add("上传文件不存在！");
                }
            }
            catch (Exception ex)
            {
                throw;
            }
            return workBook;
        }
    }
}
